﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using Deloitte.MSExcel;
using Deloitte.Data;
using System.Data;
namespace Deloitte.MSExcel
{
    public class ExcelHelper
    {
        public static Excel.Workbook OpenWorkbook(Excel.Application xlApp, string filePath)
        {
            return xlApp.Workbooks.Open(filePath, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        }

        public static Excel.Worksheet GetExcelSheet(Excel.Workbook workBook, string sheetName)
        {
            Excel.Worksheet sheet;
            try
            {
                //todo: handle when the sheet is not present - allow the user to pick            
                sheet = (Excel.Worksheet)workBook.Worksheets[sheetName];                
            }
            catch (Exception e) { throw e; }
            return sheet;
        }

        public static int GetUsedRows(Excel.Worksheet sheet)
        {
            int r = 0;
            if (sheet == null) return 1;
            Excel.Range rng = sheet.get_Range("A1", System.Reflection.Missing.Value);
            while (rng.get_Offset(r, 0).get_Value(System.Reflection.Missing.Value) != null && rng.get_Offset(r, 0).get_Value(System.Reflection.Missing.Value).ToString().Trim() != "") r++;
            return r;
        }

        public static int GetUsedRows(Excel.Worksheet sheet, string column)
        {
            int r = 0;
            if (sheet == null) return 1;
            Excel.Range rng = sheet.get_Range(column + "1", System.Reflection.Missing.Value);
            while (rng.get_Offset(r, 0).get_Value(System.Reflection.Missing.Value) != null && rng.get_Offset(r, 0).get_Value(System.Reflection.Missing.Value).ToString().Trim() != "") r++;
            return r;
        }

        public static Excel.Range GetUsedRange(Excel.Worksheet sheet)
        {
            Excel.Range range = sheet.get_Range("A1:" + GetUsedColumns(sheet) + GetUsedRows(sheet).ToString(), System.Reflection.Missing.Value);
            return range;
        }

        public static string GetUsedColumns(Microsoft.Office.Interop.Excel.Worksheet sheet)
        {
            int r = 0;
            Excel.Range rng = sheet.get_Range("A1", System.Reflection.Missing.Value);
            while (rng.get_Offset(0, r).get_Value(System.Reflection.Missing.Value) != null && rng.get_Offset(0, r).get_Value(System.Reflection.Missing.Value).ToString().Trim() != "") r++;
            string address = rng.get_Offset(0, r).get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            return address.Substring(1, address.IndexOf(System.Convert.ToChar("$"), 1) - 1);
        }

        public static bool ValidateSheet(Excel.Workbook xlWorkbook, string worksheetName)
        {
            try
            {
                Excel.Worksheet mySheet;
                mySheet = (Excel.Worksheet)xlWorkbook.Worksheets[worksheetName];
                return true;
            }
            catch { return false; }
        }

        public static bool ValidateRange(Excel.Worksheet xlWorksheet, string rangeAddress)
        {
            try
            {
                Excel.Range myRange;
                myRange = (Excel.Range)xlWorksheet.get_Range(rangeAddress, System.Type.Missing);
                if (myRange != null) return true;
                else { return false; }
            }
            catch { return false; }
        }
        public static string[] GetSheetNames(Excel.Workbook xlWorkbook)
        {
            string[] sheets = new string[xlWorkbook.Worksheets.Count];
            int i = 0;
            foreach (Excel.Worksheet sheet in xlWorkbook.Worksheets)
            {
                sheets[i] = sheet.Name;
                i++;
            }
            return sheets;
        }
        public static string ConvertDotNetTypeToNumberFormat(System.Type type)
        {
            if (type == typeof(string)) { return "@"; }
            else if (type == typeof(double)) { return "0.00"; }
            else if (type == typeof(int) || type == typeof(Int16) || type == typeof(Int32) || type == typeof(Int64)) { return "0"; }
            else { return "General"; }
        }
        public static DataTable2 LoadRangeToDataTable(string fileName, string worksheetName, string rangeAddress, bool isColumnHeadersIncluded, bool setDataTypesFromNumberFormat, string[] colNameArray /* = null */)
        {
            if (rangeAddress.Contains(":") == false) throw new Exception("Range address is not well formed.");
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                object missing = System.Reflection.Missing.Value;
                Excel.Workbook book = xlApp.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets[worksheetName];
                string[] splitRange = rangeAddress.Split(System.Convert.ToChar(":"));
                Excel.Range range = sheet.get_Range(splitRange[0], splitRange[1]);
                return ExcelHelper.LoadRangeToDataTable(range, isColumnHeadersIncluded, setDataTypesFromNumberFormat, colNameArray);
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                xlApp.Quit();
                xlApp = null;
                System.GC.WaitForPendingFinalizers();
                System.GC.WaitForPendingFinalizers();
            }
        }
        public static DataTable2 LoadRangeToDataTable(Microsoft.Office.Interop.Excel.Range myRange, bool isColumnHeadersIncluded, bool setDataTypesFromNumberFormat, string[] colNameArray /* = null */)
        {
            //todo: convert to readable code
            DataTable2 table;
            try
            {
                DataRow row;
                Int32 num1;
                Int32 num2;
                DataTable2 table2 = new DataTable2();
                object[,] objArray1 = (object[,])myRange.get_Value(Missing.Value);
                Int32 num3 = (Int32)objArray1.GetUpperBound(1);
                Type[] typeArray1 = new Type[(num3 - 1) + 1];
                if (setDataTypesFromNumberFormat)
                {
                    string text2 = myRange.get_Address(false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Missing.Value, Missing.Value);
                    Excel.Worksheet worksheet1 = myRange.Worksheet;
                    string text4 = text2.Substring(0, text2.IndexOf(":"));
                    string text3 = text2.Substring(text2.IndexOf(":") + 1, text2.Length - (text2.IndexOf(":") + 1));
                    for (num1 = 0; worksheet1.get_Range(text4, Missing.Value).get_Offset(0, num1).Column <= worksheet1.get_Range(text3, Missing.Value).Column; num1 = (Int32)(num1 + 1))
                    {
                        typeArray1[num1] = typeof(string);
                    }
                }
                Int32 num8 = num3;
                num1 = 1;
                while (num1 <= num8)
                {
                    string text1;
                    try
                    {
                        if (isColumnHeadersIncluded)
                        {
                            text1 = (string)objArray1[1, num1].ToString();
                        }
                        else
                        {
                            text1 = "Column" + num1.ToString();
                        }
                        if (setDataTypesFromNumberFormat)
                        {
                            try
                            {
                                table2.Columns.Add(text1, typeArray1[num1 - 1]);
                            }
                            catch (Exception exception8)
                            {

                                Exception exception1 = exception8;
                                table2.Columns.Add(text1, typeof(string));

                            }
                        }
                        else
                        {
                            table2.Columns.Add(text1, typeof(string));
                        }
                    }
                    catch (Exception exception9)
                    {

                        Exception exception2 = exception9;
                        text1 = "Column" + num1.ToString();
                        table2.Columns.Add(text1, typeof(string));

                    }
                    num1 = (Int32)(num1 + 1);
                }
                if (isColumnHeadersIncluded)
                {
                    Int32 num7 = (Int32)objArray1.GetUpperBound(0);
                    for (num2 = 2; num2 <= num7; num2 = (Int32)(num2 + 1))
                    {
                        row = table2.NewRow();
                        Int32 num6 = num3;
                        for (num1 = 1; num1 <= num6; num1 = (Int32)(num1 + 1))
                        {
                            try
                            {
                                row[num1 - 1] = (object)objArray1[num2, num1];
                            }
                            catch (NullReferenceException exception10)
                            {

                                NullReferenceException exception3 = exception10;
                                row[num1 - 1] = DBNull.Value;

                            }
                            catch (Exception exception11)
                            {

                                Exception exception4 = exception11;
                                row[num1 - 1] = DBNull.Value;

                            }
                        }
                        table2.Rows.Add(row);
                    }
                    return table2;
                }
                Int32 num5 = (Int32)objArray1.GetUpperBound(0);
                for (num2 = 1; num2 <= num5; num2 = (Int32)(num2 + 1))
                {
                    row = table2.NewRow();
                    Int32 num4 = num3;
                    for (num1 = 1; num1 <= num4; num1 = (Int32)(num1 + 1))
                    {
                        try
                        {
                            row[num1 - 1] = (object)objArray1[num2, num1];
                        }
                        catch (NullReferenceException exception12)
                        {

                            NullReferenceException exception5 = exception12;
                            row[num1 - 1] = DBNull.Value;

                        }
                        catch (Exception exception13)
                        {

                            Exception exception6 = exception13;
                            row[num1 - 1] = DBNull.Value;

                        }
                    }
                    table2.Rows.Add(row);
                }
                table = table2;
            }
            catch (Exception exception14)
            {
                table = null;
                return table;
            }
            return table;
        }

    }
}
